/****** Object:  Stored Procedure [dbo].spBienBanKiemKeKhoGetList Script Date: 7/16/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spDanhSachThuocHetHan]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spDanhSachThuocHetHan]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		17/07/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE spDanhSachThuocHetHan
	@songay int
as
	Select sp.MaThuoc, sp.TenHoatChat, sp.TenBietDuoc, sp.NongDoHamLuong, dvt.TenDonVi, tbl_LuuKho.SoLo, 
	       tbl_LuuKho.TonKho, tbl_LuuKho.NgaySX, tbl_LuuKho.HanSuDung, tbl_LuuKho.HanDung
	From tbl_ChiTietPhieuNhap as ctpn 
	INNER JOIN tbl_LuuKho ON tbl_LuuKho.MaLuuKho = ctpn.MaLuuKho
	INNER JOIN tbl_SanPham sp ON sp.MaThuoc = tbl_LuuKho.MaThuoc
	INNER JOIN tbl_DonViTinh dvt ON sp.MaDonVi = dvt.MaDonVi
	Where tbl_LuuKho.TonKho > 0 --cu tbl_LuuKho.NgaySX is not null AND tbl_LuuKho.TonKho > 0
	      AND (DATEDIFF(day, tbl_LuuKho.HanSuDung, getdate()) + 1) <= @songay
go

--GRANT EXECUTE ON [dbo].[spBienBanKiemKeKhoGetList] TO [sa]
--GO
